﻿    using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace QuanLiNhaSachDAO
{
    public class ThongKeDAO
    {
        public static DataTable thongKeNhapSachTheoTuaDe(string tuNgay, string denNgay)
        {
            string sql = "";
       /*     sql += "SELECT SACH.id AS [id], ";
		    sql += "SACH.Ten AS [Ten], ";
		    sql += "SACH.GiaMua AS [GiaMua], ";
		    sql += "SUM(CHITIETPHIEUNHAP.SoLuong) AS [SoLuong], ";
            sql += "SUM(CHITIETPHIEUNHAP.SoLuong*SACH.GiaMua) AS [TongTien] ";
            sql += "FROM CHITIETPHIEUNHAP ";
	        sql += "INNER JOIN SACH ON CHITIETPHIEUNHAP.MaSach = SACH.id ";
	        sql += "INNER JOIN PHIEUNHAP ON CHITIETPHIEUNHAP.MaPhieuNhap = PHIEUNHAP.id ";
            sql += "WHERE PHIEUNHAP.Ngay >= '" + tuNgay + "' AND PHIEUNHAP.Ngay <= '" + denNgay + "'";
            sql += "GROUP BY SACH.id, SACH.Ten, SACH.GiaMua ";
            sql += "ORDER BY SUM(CHITIETPHIEUNHAP.SoLuong) DESC";*/

            sql += "SELECT Results.id, Results.Ten, REPLACE(REPLACE(REPLACE((SELECT CAST(TACGIA.Ten AS NVARCHAR(MAX)) AS Expr1 FROM SACHTACGIA INNER JOIN TACGIA ON SACHTACGIA.MaTacGia = TACGIA.id WHERE (SACHTACGIA.MaSach = Results.id) FOR XML PATH('')), '</Expr1><Expr1>', ', '), '<Expr1>', ''), '</Expr1>', '') AS TacGia, Results.GiaMua, SUM(CHITIETPHIEUNHAP.SoLuong) AS [LuongNhap], SUM(CHITIETPHIEUNHAP.SoLuong*Results.GiaMua) AS [TongTien] FROM SACH AS Results INNER JOIN NHACUNGCAP ON Results.MaNhaCungCap = NHACUNGCAP.id ";
            sql += "INNER JOIN CHITIETPHIEUNHAP ON CHITIETPHIEUNHAP.MaSach = Results.id ";
            sql += "INNER JOIN PHIEUNHAP ON CHITIETPHIEUNHAP.MaPhieuNhap = PHIEUNHAP.id ";
            sql += "WHERE PHIEUNHAP.Ngay >= '" + tuNgay + "' AND PHIEUNHAP.Ngay <= '" + denNgay + "'";
            sql += "GROUP BY Results.id, Results.Ten, Results.GiaMua ";
            
            /*sql += "SELECT Results.id, Results.Ten, Results.GiaMua, Results.GiaBan, NHACUNGCAP.Ten AS NhaCungCap, SUM(CHITIETPHIEUNHAP.SoLuong) AS [LuongNhap], Results.SoLuong, REPLACE(REPLACE(REPLACE((SELECT CAST(NHAXUATBAN.Ten AS NVARCHAR(MAX)) AS Expr1 FROM SACHNHAXUATBAN INNER JOIN NHAXUATBAN ON SACHNHAXUATBAN.MaNhaXuatBan = NHAXUATBAN.id WHERE (SACHNHAXUATBAN.MaSach = Results.id) FOR XML PATH('')), '</Expr1><Expr1>', ', '), '<Expr1>', ''), '</Expr1>', '') AS NhaXuatBan, REPLACE(REPLACE(REPLACE((SELECT CAST(THELOAI.Ten AS NVARCHAR(MAX)) AS Expr1 FROM SACHTHELOAI INNER JOIN THELOAI ON SACHTHELOAI.MaTheLoai = THELOAI.id WHERE (SACHTHELOAI.MaSach = Results.id) FOR XML PATH('')), '</Expr1><Expr1>', ', '), '<Expr1>', ''), '</Expr1>', '') AS TheLoai, REPLACE(REPLACE(REPLACE((SELECT CAST(TACGIA.Ten AS NVARCHAR(MAX)) AS Expr1 FROM SACHTACGIA INNER JOIN TACGIA ON SACHTACGIA.MaTacGia = TACGIA.id WHERE (SACHTACGIA.MaSach = Results.id) FOR XML PATH('')), '</Expr1><Expr1>', ', '), '<Expr1>', ''), '</Expr1>', '') AS TacGia ";
            sql += "FROM SACH AS Results INNER JOIN NHACUNGCAP ON Results.MaNhaCungCap = NHACUNGCAP.id ";
            sql += "INNER JOIN CHITIETPHIEUNHAP ON Results.id = CHITIETPHIEUNHAP.MaSach ";
            sql += "INNER JOIN PHIEUNHAP ON CHITIETPHIEUNHAP.MaPhieuNhap = PHIEUNHAP.id ";
            sql += "WHERE PHIEUNHAP.Ngay >= '" + tuNgay + "' AND PHIEUNHAP.Ngay <= '" + denNgay + "'";
            sql += "GROUP BY Results.id, Results.Ten, Results.GiaMua, Results.GiaBan, NHACUNGCAP.Ten, Results.SoLuong ";
            sql += "ORDER BY Results.id ASC";*/
            SqlDataAdapter da = new SqlDataAdapter(sql, sqlDB.MyConnection);
            DataTable dt = new DataTable();
            da.Fill(dt);
         /*   dt.Columns.Add("Tác Giả");
            dt.Columns.Add("Thể Loại");
            dt.Columns.Add("Nhà xuất bản");
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                dt.Rows[i]["Tác Giả"] = TacGiaDAO.TimTacGia(dt.Rows[i][0].ToString());
                dt.Rows[i]["Thể Loại"] = TheLoaiDAO.TimTheLoai(dt.Rows[i][0].ToString());
                dt.Rows[i]["Nhà xuất bản"] = NhaXuatBanDAO.TimNXB(dt.Rows[i][0].ToString());
            }*/
            return dt;
        }

        public static DataTable thongKeBanSachTheoTuaDe(string tuNgay, string denNgay)
        {
            string sql = "";
          /*  sql += "SELECT SACH.id AS [id], ";
            sql += "SACH.Ten AS [Ten], ";
            sql += "SACH.GiaBan AS [GiaBan], ";
            sql += "SUM(CHITIETHOADON.SoLuong) AS [SoLuong], ";
            sql += "SUM(CHITIETHOADON.SoLuong*SACH.GiaBan) AS [TongTien] ";
            sql += "FROM CHITIETHOADON ";
            sql += "INNER JOIN SACH ON CHITIETHOADON.MaSach = SACH.id ";
            sql += "INNER JOIN HOADON ON CHITIETHOADON.MaHoaDon = HOADON.id ";
            sql += "WHERE HOADON.Ngay >= '" + tuNgay + "' AND HOADON.Ngay <= '" + denNgay + "'";
            sql += "GROUP BY SACH.id, SACH.Ten, SACH.GiaBan ";
            sql += "ORDER BY SUM(CHITIETHOADON.SoLuong) DESC";*/

            sql += "SELECT Results.id, Results.Ten, REPLACE(REPLACE(REPLACE((SELECT CAST(TACGIA.Ten AS NVARCHAR(MAX)) AS Expr1 FROM SACHTACGIA INNER JOIN TACGIA ON SACHTACGIA.MaTacGia = TACGIA.id WHERE (SACHTACGIA.MaSach = Results.id) FOR XML PATH('')), '</Expr1><Expr1>', ', '), '<Expr1>', ''), '</Expr1>', '') AS TacGia, Results.GiaBan, SUM(CHITIETHOADON.SoLuong) AS [LuongBan], SUM(CHITIETHOADON.SoLuong*Results.GiaBan) AS [TongTien] FROM SACH AS Results INNER JOIN NHACUNGCAP ON Results.MaNhaCungCap = NHACUNGCAP.id ";
            sql += "INNER JOIN CHITIETHOADON ON CHITIETHOADON.MaSach = Results.id ";
            sql += "INNER JOIN HOADON ON CHITIETHOADON.MaHoaDon = HOADON.id ";
            sql += "WHERE HOADON.Ngay >= '" + tuNgay + "' AND HOADON.Ngay <= '" + denNgay + "'";
            sql += "GROUP BY Results.id, Results.Ten, Results.GiaBan ";
           
            
            /*sql += "SELECT Results.id, Results.Ten, Results.GiaMua, Results.GiaBan, NHACUNGCAP.Ten AS NhaCungCap, SUM(CHITIETHOADON.SoLuong) AS [LuongBan], Results.SoLuong, REPLACE(REPLACE(REPLACE((SELECT CAST(NHAXUATBAN.Ten AS NVARCHAR(MAX)) AS Expr1 FROM SACHNHAXUATBAN INNER JOIN NHAXUATBAN ON SACHNHAXUATBAN.MaNhaXuatBan = NHAXUATBAN.id WHERE (SACHNHAXUATBAN.MaSach = Results.id) FOR XML PATH('')), '</Expr1><Expr1>', ', '), '<Expr1>', ''), '</Expr1>', '') AS NhaXuatBan, REPLACE(REPLACE(REPLACE((SELECT CAST(THELOAI.Ten AS NVARCHAR(MAX)) AS Expr1 FROM SACHTHELOAI INNER JOIN THELOAI ON SACHTHELOAI.MaTheLoai = THELOAI.id WHERE (SACHTHELOAI.MaSach = Results.id) FOR XML PATH('')), '</Expr1><Expr1>', ', '), '<Expr1>', ''), '</Expr1>', '') AS TheLoai, REPLACE(REPLACE(REPLACE((SELECT CAST(TACGIA.Ten AS NVARCHAR(MAX)) AS Expr1 FROM SACHTACGIA INNER JOIN TACGIA ON SACHTACGIA.MaTacGia = TACGIA.id WHERE (SACHTACGIA.MaSach = Results.id) FOR XML PATH('')), '</Expr1><Expr1>', ', '), '<Expr1>', ''), '</Expr1>', '') AS TacGia ";
            sql += "FROM SACH AS Results INNER JOIN NHACUNGCAP ON Results.MaNhaCungCap = NHACUNGCAP.id ";
            sql += "INNER JOIN CHITIETHOADON ON Results.id = CHITIETHOADON.MaSach ";
            sql += "INNER JOIN HOADON ON CHITIETHOADON.MaHoaDon = HOADON.id ";
            sql += "WHERE HOADON.Ngay >= '" + tuNgay + "' AND HOADON.Ngay <= '" + denNgay + "'";
            sql += "GROUP BY Results.id, Results.Ten, Results.GiaMua, Results.GiaBan, NHACUNGCAP.Ten, Results.SoLuong ";
            sql += "ORDER BY Results.id ASC";*/
            SqlDataAdapter da = new SqlDataAdapter(sql, sqlDB.MyConnection);
            DataTable dt = new DataTable();
            da.Fill(dt);
          /*  dt.Columns.Add("Tác Giả");
            dt.Columns.Add("Thể Loại");
            dt.Columns.Add("Nhà xuất bản");
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                dt.Rows[i]["Tác Giả"] = TacGiaDAO.TimTacGia(dt.Rows[i][0].ToString());
                dt.Rows[i]["Thể Loại"] = TheLoaiDAO.TimTheLoai(dt.Rows[i][0].ToString());
                dt.Rows[i]["Nhà xuất bản"] = NhaXuatBanDAO.TimNXB(dt.Rows[i][0].ToString());
            }*/
            return dt;
        }

        public static DataTable thongKeNhapSachTheoTheLoai(string tuNgay, string denNgay)
        {
            string sql = "";
            sql += "SELECT THELOAI.id AS [MaTheLoai], ";
            sql += "THELOAI.Ten AS [TenTheLoai], ";
		    sql += "SUM(CHITIETPHIEUNHAP.SoLuong) AS [SoLuong], ";
            sql += "SUM(CHITIETPHIEUNHAP.SoLuong*SACH.GiaMua) AS [TongTien] ";
            sql += "FROM CHITIETPHIEUNHAP ";
	        sql += "INNER JOIN SACH ON CHITIETPHIEUNHAP.MaSach = SACH.id ";
            sql += "INNER JOIN SACHTHELOAI ON SACH.id = SACHTHELOAI.MaSach ";
	        sql += "INNER JOIN THELOAI ON THELOAI.id = SACHTHELOAI.MaTheLoai ";
	        sql += "INNER JOIN PHIEUNHAP ON CHITIETPHIEUNHAP.MaPhieuNhap = PHIEUNHAP.id ";
            sql += "WHERE PHIEUNHAP.Ngay >= '" + tuNgay + "' AND PHIEUNHAP.Ngay <= '" + denNgay + "'";
            sql += "GROUP BY THELOAI.id, THELOAI.Ten ";
            sql += "ORDER BY SUM(CHITIETPHIEUNHAP.SoLuong) DESC";
            SqlDataAdapter da = new SqlDataAdapter(sql, sqlDB.MyConnection);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }

        public static DataTable thongKeBanSachTheoTheLoai(string tuNgay, string denNgay)
        {
            string sql = "";
            sql += "SELECT THELOAI.id AS [MaTheLoai], ";
            sql += "THELOAI.Ten AS [TenTheLoai], ";
            sql += "SUM(CHITIETHOADON.SoLuong) AS [SoLuong], ";
            sql += "SUM(CHITIETHOADON.SoLuong*SACH.GiaBan) AS [TongTien] ";
            sql += "FROM CHITIETHOADON ";
            sql += "INNER JOIN SACH ON CHITIETHOADON.MaSach = SACH.id ";
            sql += "INNER JOIN SACHTHELOAI ON SACH.id = SACHTHELOAI.MaSach ";
            sql += "INNER JOIN THELOAI ON THELOAI.id = SACHTHELOAI.MaTheLoai ";
            sql += "INNER JOIN HOADON ON CHITIETHOADON.MaHoaDon = HOADON.id ";
            sql += "WHERE HOADON.Ngay >= '" + tuNgay + "' AND HOADON.Ngay <= '" + denNgay + "'";
            sql += "GROUP BY THELOAI.id, THELOAI.Ten ";
            sql += "ORDER BY SUM(CHITIETHOADON.SoLuong) DESC";
            SqlDataAdapter da = new SqlDataAdapter(sql, sqlDB.MyConnection);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }

        public static DataTable thongKeNhapSachTheoNhanVien(string tuNgay, string denNgay)
        {
            string sql = "";
            sql += "SELECT NHANVIEN.id AS [MaNhanVien], ";
		    sql += "NHANVIEN.Ten AS [TenNhanVien], ";
		    sql += "SUM(CHITIETPHIEUNHAP.SoLuong) AS [SoLuong], ";
		    sql += "SUM(PHIEUNHAP.TongTien) AS [TongTien] ";
            sql += "FROM CHITIETPHIEUNHAP ";
	        sql += "INNER JOIN PHIEUNHAP ON PHIEUNHAP.id = CHITIETPHIEUNHAP.MaPhieuNhap ";
	        sql += "INNER JOIN NHANVIEN ON PHIEUNHAP.MaNhanVien = NHANVIEN.id ";
            sql += "WHERE PHIEUNHAP.Ngay >= '" + tuNgay + "' AND PHIEUNHAP.Ngay <= '" + denNgay + "'";
            sql += "GROUP BY NHANVIEN.id, NHANVIEN.Ten ";
            sql += "ORDER BY SUM(CHITIETPHIEUNHAP.SoLuong) DESC";
            SqlDataAdapter da = new SqlDataAdapter(sql, sqlDB.MyConnection);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }

        public static DataTable thongKeBanSachTheoNhanVien(string tuNgay, string denNgay)
        {
            string sql = "";
            sql += "SELECT NHANVIEN.id AS [MaNhanVien], ";
            sql += "NHANVIEN.Ten AS [TenNhanVien], ";
            sql += "SUM(CHITIETHOADON.SoLuong) AS [SoLuong], ";
            sql += "SUM(HOADON.TongTien) AS [TongTien] ";
            sql += "FROM CHITIETHOADON ";
            sql += "INNER JOIN HOADON ON HOADON.id = CHITIETHOADON.MaHoaDon ";
            sql += "INNER JOIN NHANVIEN ON HOADON.MaNhanVien = NHANVIEN.id ";
            sql += "WHERE HOADON.Ngay >= '" + tuNgay + "' AND HOADON.Ngay <= '" + denNgay + "'";
            sql += "GROUP BY NHANVIEN.id, NHANVIEN.Ten ";
            sql += "ORDER BY SUM(CHITIETHOADON.SoLuong) DESC";
            SqlDataAdapter da = new SqlDataAdapter(sql, sqlDB.MyConnection);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }

        public static DataTable thongKeSachKhongBanDuocTheoTuaDe(string tuNgay, string denNgay)
        {
            string sql = "";
          /*  sql += "SELECT DISTINCT SACH.id AS [id], ";
            sql += "SACH.Ten AS [Ten], ";
            sql += "SACH.GiaBan AS [GiaBan], ";
            sql += "SUM(SACH.SoLuong) AS [SoLuongTon], ";
            sql += "SUM(SACH.SoLuong*SACH.GiaBan) AS [TongTien] ";
            sql += "FROM SACH ";
            sql += "WHERE SACH.id NOT IN";*/
            sql += "SELECT Results.id, Results.Ten, REPLACE(REPLACE(REPLACE((SELECT CAST(TACGIA.Ten AS NVARCHAR(MAX)) AS Expr1 FROM SACHTACGIA INNER JOIN TACGIA ON SACHTACGIA.MaTacGia = TACGIA.id WHERE (SACHTACGIA.MaSach = Results.id) FOR XML PATH('')), '</Expr1><Expr1>', ', '), '<Expr1>', ''), '</Expr1>', '') AS TacGia, Results.GiaBan, Results.SoLuong, Results.SoLuong*Results.GiaBan AS [TongTien] FROM SACH AS Results INNER JOIN NHACUNGCAP ON Results.MaNhaCungCap = NHACUNGCAP.id ";

           // sql += "WHERE HOADON.Ngay >= '" + tuNgay + "' AND HOADON.Ngay <= '" + denNgay + "'";
            

          //  sql += "SELECT Results.id, Results.Ten, Results.GiaMua, Results.GiaBan, NHACUNGCAP.Ten AS NhaCungCap, Results.SoLuong, REPLACE(REPLACE(REPLACE((SELECT CAST(NHAXUATBAN.Ten AS NVARCHAR(MAX)) AS Expr1 FROM SACHNHAXUATBAN INNER JOIN NHAXUATBAN ON SACHNHAXUATBAN.MaNhaXuatBan = NHAXUATBAN.id WHERE (SACHNHAXUATBAN.MaSach = Results.id) FOR XML PATH('')), '</Expr1><Expr1>', ', '), '<Expr1>', ''), '</Expr1>', '') AS NhaXuatBan, REPLACE(REPLACE(REPLACE((SELECT CAST(THELOAI.Ten AS NVARCHAR(MAX)) AS Expr1 FROM SACHTHELOAI INNER JOIN THELOAI ON SACHTHELOAI.MaTheLoai = THELOAI.id WHERE (SACHTHELOAI.MaSach = Results.id) FOR XML PATH('')), '</Expr1><Expr1>', ', '), '<Expr1>', ''), '</Expr1>', '') AS TheLoai, REPLACE(REPLACE(REPLACE((SELECT CAST(TACGIA.Ten AS NVARCHAR(MAX)) AS Expr1 FROM SACHTACGIA INNER JOIN TACGIA ON SACHTACGIA.MaTacGia = TACGIA.id WHERE (SACHTACGIA.MaSach = Results.id) FOR XML PATH('')), '</Expr1><Expr1>', ', '), '<Expr1>', ''), '</Expr1>', '') AS TacGia ";
           // sql += "FROM SACH AS Results INNER JOIN NHACUNGCAP ON Results.MaNhaCungCap = NHACUNGCAP.id ";
            sql += "WHERE Results.id NOT IN";
            
            sql += "(SELECT DISTINCT SACH.id ";
            sql += "FROM CHITIETHOADON ";
            sql += "INNER JOIN SACH ON CHITIETHOADON.MaSach = SACH.id ";
            sql += "INNER JOIN HOADON ON CHITIETHOADON.MaHoaDon = HOADON.id ";
            sql += "WHERE HOADON.Ngay >= '" + tuNgay + "' AND HOADON.Ngay <= '" + denNgay + "') ";
            //sql += "GROUP BY Results.id, Results.Ten, Results.GiaMua, Results.GiaBan, NHACUNGCAP.Ten, Results.SoLuong ";
            //sql += "GROUP BY SACH.id, SACH.Ten, SACH.GiaBan ";
           // sql += "ORDER BY SACH.id ASC";
            sql += "GROUP BY Results.id, Results.Ten, Results.GiaBan, Results.SoLuong ";
            SqlDataAdapter da = new SqlDataAdapter(sql, sqlDB.MyConnection);
            DataTable dt = new DataTable();
            da.Fill(dt);
         /*   dt.Columns.Add("Tác Giả");
            dt.Columns.Add("Thể Loại");
            dt.Columns.Add("Nhà xuất bản");
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                dt.Rows[i]["Tác Giả"] = TacGiaDAO.TimTacGia(dt.Rows[i][0].ToString());
                dt.Rows[i]["Thể Loại"] = TheLoaiDAO.TimTheLoai(dt.Rows[i][0].ToString());
                dt.Rows[i]["Nhà xuất bản"] = NhaXuatBanDAO.TimNXB(dt.Rows[i][0].ToString());
            }*/
            return dt;
        }

        public static DataTable thongKeSachKhongBanDuocTheoTheLoai(string tuNgay, string denNgay)
        {
            string sql = "";
            sql += "SELECT DISTINCT  ";
            sql += "THELOAI.id [MaTheLoai], ";
            sql += "THELOAI.Ten AS [TenTheLoai], ";
            sql += "SUM(SACH.SoLuong) AS [SoLuongTon], ";
            sql += "SUM(SACH.SoLuong*SACH.GiaBan) AS [TongTien] ";
            sql += "FROM THELOAI ";
            sql += "FULL JOIN SACHTHELOAI ON THELOAI.id = SACHTHELOAI.MaTheLoai ";
            sql += "FULL JOIN SACH ON SACHTHELOAI.MaSach = SACH.id ";
            sql += "WHERE THELOAI.id NOT IN (";
            sql += "SELECT DISTINCT THELOAI.id ";
            sql += "FROM THELOAI ";
            sql += "INNER JOIN SACHTHELOAI ON THELOAI.id = SACHTHELOAI.MaTheLoai ";
            sql += "INNER JOIN CHITIETHOADON ON CHITIETHOADON.MaSach = SACHTHELOAI.MaSach ";
            sql += "INNER JOIN HOADON ON CHITIETHOADON.MaHoaDon = HOADON.id ";
            sql += "WHERE HOADON.Ngay >= '" + tuNgay + "' AND HOADON.Ngay <= '" + denNgay + "')";
            sql += "GROUP BY THELOAI.id, THELOAI.Ten";
            SqlDataAdapter da = new SqlDataAdapter(sql, sqlDB.MyConnection);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }

        public static DataTable thongKeNhapSachTheoNXB(string tuNgay, string denNgay)
        {
            string sql = "";
            sql += "SELECT NHAXUATBAN.id AS [MaNhaXuatBan], ";
            sql += "NHAXUATBAN.Ten AS [TenNhaXuatBan], ";
            sql += "SUM(CHITIETPHIEUNHAP.SoLuong) AS [SoLuong], ";
            sql += "SUM(CHITIETPHIEUNHAP.SoLuong*SACH.GiaMua) AS [TongTien] ";
            sql += "FROM CHITIETPHIEUNHAP ";
            sql += "INNER JOIN SACH ON CHITIETPHIEUNHAP.MaSach = SACH.id ";
            sql += "INNER JOIN SACHNHAXUATBAN ON SACH.id = SACHNHAXUATBAN.MaSach ";
            sql += "INNER JOIN NHAXUATBAN ON NHAXUATBAN.id = SACHNHAXUATBAN.MaNhaXuatBan ";
            sql += "INNER JOIN PHIEUNHAP ON CHITIETPHIEUNHAP.MaPhieuNhap = PHIEUNHAP.id ";
            sql += "WHERE PHIEUNHAP.Ngay >= '" + tuNgay + "' AND PHIEUNHAP.Ngay <= '" + denNgay + "'";
            sql += "GROUP BY NHAXUATBAN.id, NHAXUATBAN.Ten ";
            sql += "ORDER BY SUM(CHITIETPHIEUNHAP.SoLuong) DESC";
            SqlDataAdapter da = new SqlDataAdapter(sql, sqlDB.MyConnection);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }

        public static DataTable thongKeBanSachTheoNXB(string tuNgay, string denNgay)
        {
            string sql = "";
            sql += "SELECT NHAXUATBAN.id AS [MaNhaXuatBan], ";
            sql += "NHAXUATBAN.Ten AS [TenNhaXuatBan], ";
            sql += "SUM(CHITIETHOADON.SoLuong) AS [SoLuong], ";
            sql += "SUM(CHITIETHOADON.SoLuong*SACH.GiaBan) AS [TongTien] ";
            sql += "FROM CHITIETHOADON ";
            sql += "INNER JOIN SACH ON CHITIETHOADON.MaSach = SACH.id ";
            sql += "INNER JOIN SACHNHAXUATBAN ON SACH.id = SACHNHAXUATBAN.MaSach ";
            sql += "INNER JOIN NHAXUATBAN ON NHAXUATBAN.id = SACHNHAXUATBAN.MaNhaXuatBan ";
            sql += "INNER JOIN HOADON ON CHITIETHOADON.MaHoaDon = HOADON.id ";
            sql += "WHERE HOADON.Ngay >= '" + tuNgay + "' AND HOADON.Ngay <= '" + denNgay + "'";
            sql += "GROUP BY NHAXUATBAN.id, NHAXUATBAN.Ten ";
            sql += "ORDER BY SUM(CHITIETHOADON.SoLuong) DESC";
            SqlDataAdapter da = new SqlDataAdapter(sql, sqlDB.MyConnection);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }
        public static DataTable thongKeSachKhongBanDuocTheoNXB(string tuNgay, string denNgay)
        {
            string sql = "";
            sql += "SELECT DISTINCT  ";
            sql += "NHAXUATBAN.id [MaNhaXuatBan], ";
            sql += "NHAXUATBAN.Ten AS [TenNhaXuatBan], ";
            sql += "SUM(SACH.SoLuong) AS [SoLuongTon], ";
            sql += "SUM(SACH.SoLuong*SACH.GiaBan) AS [TongTien] ";
            sql += "FROM NHAXUATBAN ";
            sql += "FULL JOIN SACHNHAXUATBAN ON NHAXUATBAN.id = SACHNHAXUATBAN.MaNhaXuatBan ";
            sql += "FULL JOIN SACH ON SACHNHAXUATBAN.MaSach = SACH.id ";
            sql += "WHERE NHAXUATBAN.id NOT IN (";
            sql += "SELECT DISTINCT NHAXUATBAN.id ";
            sql += "FROM NHAXUATBAN ";
            sql += "INNER JOIN SACHNHAXUATBAN ON NHAXUATBAN.id = SACHNHAXUATBAN.MaNhaXuatBan ";
            sql += "INNER JOIN CHITIETHOADON ON CHITIETHOADON.MaSach = SACHNHAXUATBAN.MaSach ";
            sql += "INNER JOIN HOADON ON CHITIETHOADON.MaHoaDon = HOADON.id ";
            sql += "WHERE HOADON.Ngay >= '" + tuNgay + "' AND HOADON.Ngay <= '" + denNgay + "')";
            sql += "GROUP BY NHAXUATBAN.id, NHAXUATBAN.Ten";
            SqlDataAdapter da = new SqlDataAdapter(sql, sqlDB.MyConnection);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }

        public static DataTable thongKeNhapSachTheoTacGia(string tuNgay, string denNgay)
        {
            string sql = "";
            sql += "SELECT TACGIA.id AS [MaTacGia], ";
            sql += "TACGIA.Ten AS [TenTacGia], ";
            sql += "SUM(CHITIETPHIEUNHAP.SoLuong) AS [SoLuong], ";
            sql += "SUM(CHITIETPHIEUNHAP.SoLuong*SACH.GiaMua) AS [TongTien] ";
            sql += "FROM CHITIETPHIEUNHAP ";
            sql += "INNER JOIN SACH ON CHITIETPHIEUNHAP.MaSach = SACH.id ";
            sql += "INNER JOIN SACHTACGIA ON SACH.id = SACHTACGIA.MaSach ";
            sql += "INNER JOIN TACGIA ON TACGIA.id = SACHTACGIA.MaTacGia ";
            sql += "INNER JOIN PHIEUNHAP ON CHITIETPHIEUNHAP.MaPhieuNhap = PHIEUNHAP.id ";
            sql += "WHERE PHIEUNHAP.Ngay >= '" + tuNgay + "' AND PHIEUNHAP.Ngay <= '" + denNgay + "'";
            sql += "GROUP BY TACGIA.id, TACGIA.Ten ";
            sql += "ORDER BY SUM(CHITIETPHIEUNHAP.SoLuong) DESC";
            SqlDataAdapter da = new SqlDataAdapter(sql, sqlDB.MyConnection);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }

        public static DataTable thongKeBanSachTheoTacGia(string tuNgay, string denNgay)
        {
            string sql = "";
            sql += "SELECT TACGIA.id AS [MaTacGia], ";
            sql += "TACGIA.Ten AS [TenTacGia], ";
            sql += "SUM(CHITIETHOADON.SoLuong) AS [SoLuong], ";
            sql += "SUM(CHITIETHOADON.SoLuong*SACH.GiaBan) AS [TongTien] ";
            sql += "FROM CHITIETHOADON ";
            sql += "INNER JOIN SACH ON CHITIETHOADON.MaSach = SACH.id ";
            sql += "INNER JOIN SACHTACGIA ON SACH.id = SACHTACGIA.MaSach ";
            sql += "INNER JOIN TACGIA ON TACGIA.id = SACHTACGIA.MaTacGia ";
            sql += "INNER JOIN HOADON ON CHITIETHOADON.MaHoaDon = HOADON.id ";
            sql += "WHERE HOADON.Ngay >= '" + tuNgay + "' AND HOADON.Ngay <= '" + denNgay + "'";
            sql += "GROUP BY TACGIA.id, TACGIA.Ten ";
            sql += "ORDER BY SUM(CHITIETHOADON.SoLuong) DESC";
            SqlDataAdapter da = new SqlDataAdapter(sql, sqlDB.MyConnection);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }
        public static DataTable thongKeSachKhongBanDuocTheoTacGia(string tuNgay, string denNgay)
        {
            string sql = "";
            sql += "SELECT DISTINCT  ";
            sql += "TACGIA.id [MaTacGia], ";
            sql += "TACGIA.Ten AS [TenTacGia], ";
            sql += "SUM(SACH.SoLuong) AS [SoLuongTon], ";
            sql += "SUM(SACH.SoLuong*SACH.GiaBan) AS [TongTien] ";
            sql += "FROM TACGIA ";
            sql += "FULL JOIN SACHTACGIA ON TACGIA.id = SACHTACGIA.MaTacGia ";
            sql += "FULL JOIN SACH ON SACHTACGIA.MaSach = SACH.id ";
            sql += "WHERE TACGIA.id NOT IN (";
            sql += "SELECT DISTINCT TACGIA.id ";
            sql += "FROM TACGIA ";
            sql += "INNER JOIN SACHTACGIA ON TACGIA.id = SACHTACGIA.MaTacGia ";
            sql += "INNER JOIN CHITIETHOADON ON CHITIETHOADON.MaSach = SACHTACGIA.MaSach ";
            sql += "INNER JOIN HOADON ON CHITIETHOADON.MaHoaDon = HOADON.id ";
            sql += "WHERE HOADON.Ngay >= '" + tuNgay + "' AND HOADON.Ngay <= '" + denNgay + "')";
            sql += "GROUP BY TACGIA.id, TACGIA.Ten";
            SqlDataAdapter da = new SqlDataAdapter(sql, sqlDB.MyConnection);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }

        public static DataTable thongKeNhapSachTheoNhaCungCap(string tuNgay, string denNgay)
        {
            string sql = "";
            sql += "SELECT NHACUNGCAP.id AS [MaNhaCungCap], ";
            sql += "NHACUNGCAP.Ten AS [TenNhaCungCap], ";
            sql += "SUM(CHITIETPHIEUNHAP.SoLuong) AS [SoLuong], ";
            sql += "SUM(CHITIETPHIEUNHAP.SoLuong*SACH.GiaMua) AS [TongTien]";
            sql += "FROM SACH INNER JOIN NHACUNGCAP ON SACH.MaNhaCungCap = NHACUNGCAP.id ";
            sql += "INNER JOIN CHITIETPHIEUNHAP ON SACH.id = CHITIETPHIEUNHAP.MaSach ";
            sql += "INNER JOIN PHIEUNHAP ON CHITIETPHIEUNHAP.MaPhieuNhap = PHIEUNHAP.id ";
            sql += "WHERE PHIEUNHAP.Ngay >= '" + tuNgay + "' AND PHIEUNHAP.Ngay <= '" + denNgay + "'";
            sql += "GROUP BY NHACUNGCAP.id, NHACUNGCAP.Ten ";
            sql += "ORDER BY SUM(CHITIETPHIEUNHAP.SoLuong) DESC";
            SqlDataAdapter da = new SqlDataAdapter(sql, sqlDB.MyConnection);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }

        public static DataTable thongKeBanSachTheoNhaCungCap(string tuNgay, string denNgay)
        {
            string sql = "";
            sql += "SELECT NHACUNGCAP.id AS [MaNhaCungCap], ";
            sql += "NHACUNGCAP.Ten AS [TenNhaCungCap], ";
            sql += "SUM(CHITIETHOADON.SoLuong) AS [SoLuong], ";
            sql += "SUM(CHITIETHOADON.SoLuong*SACH.GiaBan) AS [TongTien]";
            sql += "FROM SACH INNER JOIN NHACUNGCAP ON SACH.MaNhaCungCap = NHACUNGCAP.id ";
            sql += "INNER JOIN CHITIETHOADON ON SACH.id = CHITIETHOADON.MaSach ";
            sql += "INNER JOIN HOADON ON CHITIETHOADON.MaHoaDon = HOADON.id ";
            sql += "WHERE HOADON.Ngay >= '" + tuNgay + "' AND HOADON.Ngay <= '" + denNgay + "'";
            sql += "GROUP BY NHACUNGCAP.id, NHACUNGCAP.Ten ";
            sql += "ORDER BY SUM(CHITIETHOADON.SoLuong) DESC";
            SqlDataAdapter da = new SqlDataAdapter(sql, sqlDB.MyConnection);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }

        public static DataTable thongKeSachKhongBanDuocTheoNhaCungCap(string tuNgay, string denNgay)
        {
            string sql = "";
            sql += "SELECT DISTINCT NHACUNGCAP.id AS [MaNhaCungCap], ";
            sql += "NHACUNGCAP.Ten AS [TenNhaCungCap], ";
            sql += "SUM(SACH.SoLuong) AS [SoLuongTon], ";
            sql += "SUM(SACH.SoLuong*SACH.GiaBan) AS [TongTien]";
            sql += "FROM NHACUNGCAP INNER JOIN SACH ON NHACUNGCAP.id = SACH.MaNhaCungCap ";
            sql += "WHERE NHACUNGCAP.id NOT IN (";
            sql += "SELECT NHACUNGCAP.id ";
            sql += "FROM SACH INNER JOIN NHACUNGCAP ON SACH.MaNhaCungCap = NHACUNGCAP.id ";
            sql += "INNER JOIN CHITIETHOADON ON SACH.id = CHITIETHOADON.MaSach ";
            sql += "INNER JOIN HOADON ON CHITIETHOADON.MaHoaDon = HOADON.id ";
            sql += "WHERE HOADON.Ngay >= '" + tuNgay + "' AND HOADON.Ngay <= '" + denNgay + "')";
            sql += "GROUP BY NHACUNGCAP.id, NHACUNGCAP.Ten ";
            SqlDataAdapter da = new SqlDataAdapter(sql, sqlDB.MyConnection);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }
    }
}
